package com.forekonw.demo1;

import com.forekonw.demo.Employee;
import com.sun.org.apache.bcel.internal.generic.IF_ACMPEQ;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * DriverManager类
 * Connection接口  获取到连接
 * Statement接口   获取查询方法执行静态sql语句的对象
 * ResultSet接口   获取结果集
 * 执行之后需要关闭
 */



public class JDBCtest {
    Connection conn;
    Statement stat;
    ResultSet rs;
    PreparedStatement ps;

    public Connection conn(String fn, String url, String user, String password) throws ClassNotFoundException, SQLException {
        Class.forName(fn);
        conn = DriverManager.getConnection(url, user, password);
        return conn;
    }

    public ResultSet query(String string, Object... values) throws SQLException {
        ps = conn.prepareStatement(string);
        for (int i = 0; i < values.length; i++) {
            ps.setObject(i + 1, values[i]);
        }
        rs = ps.executeQuery();
        return rs;
    }

    public int dml(String string, Object... values) throws SQLException {
        ps = conn.prepareStatement(string);
        for (int i = 0; i < values.length; i++) {
            ps.setObject(i + 1, values[i]);
        }
        return ps.executeUpdate();
    }

    public void close() throws SQLException {
        if (rs != null) {
            rs.close();
        }
        if (stat != null) {
            stat.close();
        }
        if (conn != null) {
            conn.close();
        }
    }


    public static void main(String[] args) {
        String fn = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/aaa?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
        String user = "root";
        String password = "root";
        JDBCtest jdbCtest = new JDBCtest();
        try {
            Connection co = jdbCtest.conn(fn, url, user, password);

//            int a = jdbCtest.dml("insert into employee(username,password)values ('tom','15612')");
            ResultSet rs = jdbCtest.query("select * from employee where username=? and password =? ","fender", "123456");
            List<Employee> list = new ArrayList<>();
            while (rs.next()){
                int id = rs.getInt("id");
                String username = rs.getString("username");
                String pass = rs.getString("password");
                Employee employee = new Employee();
                employee.setId(id);
                employee.setUsername(username);
                employee.setPassword(pass);
                list.add(employee);
            }
            for (Employee e :list) {
                System.out.println(e.getId()+"---"+e.getUsername()+"---"+e.getPassword());
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                jdbCtest.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
//        Connection conn = null;
//        Statement stat = null;
//        ResultSet rs = null;
//        try {
//            Class.forName(fn);
//            String url = "jdbc:mysql://localhost:3306/aaa?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
//            String user = "root";
//            String password = "root";
//            conn = DriverManager.getConnection(url,user,password);
//            stat = conn.createStatement();
////            int a = stat.executeUpdate("insert into employee(username,password)values ('tom','15612')");
////            System.out.println(a);
//            rs = stat.executeQuery("select * from employee");
//            List<Employee> list = new ArrayList<>();
//            while (rs.next()){
//                int id = rs.getInt("id");
//                String username = rs.getString("username");
//                String pass = rs.getString("password");
//                Employee employee = new Employee();
//                employee.setId(id);
//                employee.setUsername(username);
//                employee.setPassword(pass);
//                list.add(employee);
//            }
//            for (Employee e :list) {
//                System.out.println(e.getId()+"---"+e.getUsername()+"---"+e.getPassword());
//            }
//        } catch (ClassNotFoundException e) {
//            e.printStackTrace();
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }finally {
//            try {
//                rs.close();
//            } catch (SQLException e) {
//                e.printStackTrace();
//            }
//            try {
//                stat.close();
//            } catch (SQLException e) {
//                e.printStackTrace();
//            }
//            try {
//                conn.close();
//            } catch (SQLException e) {
//                e.printStackTrace();
//            }
//        }
    }
}

